# coding=utf-8

import timezone_conversion
import sys
from DBConnection import DBConnect
import git
import os
import re
#from SendEmailNotification import sendNotification
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
import smtplib


if len(sys.argv) >1:
    DATABASE_LIST = [sys.argv[1].upper()]
else:
    DATABASE_LIST = ['GBOS', 'IFT', 'CARE', 'CAS', 'VIP', 'IDEA']
print('DATABASE_LIST:', DATABASE_LIST)

diff_list = []
for DATABASE in DATABASE_LIST:
    if DATABASE == 'GBOS':
        branch_aws = 'GBOS-BOSSQL-GBOS'
        branch_azure = 'GBOS-AzureSQL-GBOS'
        server = 'qabosdbag.awsnp.gdotawsnp.com'
    elif DATABASE == 'IFT':
        branch_aws = 'GBOS-BOSSQL-IFT'
        branch_azure = 'GBOS-AzureSQL-IFT'
        server = 'qabosdbag.awsnp.gdotawsnp.com'
    elif DATABASE == 'CARE':
        branch_aws = 'GBOS-BOSCRMSQL-CARE'
        branch_azure = 'GBOS-AzureSQL-CARE'
        server = 'qaboscrmdbag.awsnp.gdotawsnp.com'
    elif DATABASE == 'CAS':
        branch_aws = 'GBOS-BOSCRMSQL-CAS'
        branch_azure = 'GBOS-AzureSQL-CAS'
        server = 'qaboscrmdbag.awsnp.gdotawsnp.com'
    elif DATABASE == 'VIP':
        branch_aws = 'GBOS-BOSCRMSQL-VIP'
        branch_azure = 'GBOS-AzureSQL-VIP'
        server = 'qaboscrmdbag.awsnp.gdotawsnp.com'
    elif DATABASE == 'IDEA':
        branch_aws = 'GBOS-BOSTOKENSQL-IDEA'
        branch_azure = 'GBOS-AzureSQL-IDEA'
        server = 'QABOSTKNDBAG.awsnp.gdotawsnp.com'
    else:
        branch_aws = ''
        branch_azure = ''
        server = ''
        print('The first parameter is not valid')
        exit(1)

    # AWS DB connect configuration
    DBConnetion = DBConnect(serverName=server,databaseName=DATABASE)
    conn_aws = DBConnetion.openConn()
    cursor_aws = conn_aws.cursor()
    print('Connect to AWS QA server')

    # pull aws repo
    repo_path = rf"C:\work\code\{branch_aws}"
    repo = git.Repo(repo_path)
    if repo.active_branch != 'main':
        repo.git.checkout('main')
    #repo.remotes.origin.pull()
    print("Pull from GitHub: " + branch_aws)

    # pull azure repo
    repo_path_azure = rf"C:\work\code\{branch_azure}"
    repo_azure = git.Repo(repo_path_azure)
    if repo_azure.active_branch != 'main':
        repo_azure.git.checkout('main')
    #repo_azure.remotes.origin.pull()
    print("Pull from GitHub: " + branch_azure)

    # get sp list
    SQL = ("SELECT routine_schema + '.' + routine_name, LAST_ALTERED "
           "FROM INFORMATION_SCHEMA.ROUTINES "
           "WHERE ROUTINE_TYPE = 'PROCEDURE' "
           #"AND routine_name='InsIVRAuditHeaderExtensionDetail'"
           "")
    cursor_aws.execute(SQL)
    sp_list = cursor_aws.fetchall()
    print('sp_list:', sp_list)

    folder_path_aws = rf"C:\work\code\{branch_aws}"
    folder_path_azure = rf"C:\work\code\{branch_azure}"
    #single_diff_list = []
    diff_db_list = []
    for row in sp_list:
        sp = row[0]
        update_time = row[1]
        file_name = sp + '.sql'
        print('file_name:', file_name)

        content_aws_conversion = timezone_conversion.timezone_conversion(DATABASE, file_name, 'sp')
        #print('content_aws_conversion:', content_aws_conversion)
        if not content_aws_conversion:
            print(file_name, "doesn't exist")
        else:
            script_content_aws = re.sub(r'\n\s*\n', '\n', content_aws_conversion[3]).upper()
            script_content_aws = script_content_aws.replace(' ', '')
            script_content_aws = script_content_aws.rstrip('\n')
            script_content_aws = script_content_aws.replace('\t', '')
            #print('script_content_aws:', script_content_aws)

            script_file_path = folder_path_azure + r'\Stored Procedures\\' + file_name
            if os.path.exists(script_file_path):
                print(script_file_path, 'exists')
                with (open(script_file_path, 'r') as file):
                    script_content_azure = file.read()
                    script_content_azure = script_content_azure.replace(' ', '').upper()  #remove blank space
                    script_content_azure = re.sub(r'\n\s*\n', '\n', script_content_azure)  #remove blank line
                    script_content_azure = script_content_azure.rstrip('\n')  #remove last blank line
                    script_content_azure = script_content_azure.replace('\t', '')  #remove tabs
                    #print('script_content_azure:', script_content_azure)
                    if script_content_aws != script_content_azure:
                        diff_db = (file_name, update_time)
                        diff_db_list.append(diff_db)
                        # diff_list.append(file_name)
                        print(file_name, 'is different.')
                    else:
                        print(sp, 'is the same.')
            else:
                print(script_file_path, "doesn't exist")

    diff_list.append(diff_db_list)
print('diff_list:', diff_list)
print('Compare has completed')
cursor_aws.close()
conn_aws.close()


# send email
smtpObj = smtplib.SMTP('gdcsmtp01.nextestate.com')
mail_msg = '<br>Hello Team:<br><br>This mail is sent out by automation job. It is about compare between AWS-QA and AZURE-QA environment.<br>'
mail_msg2 = 'You might miss syncing SP to AZURE-QA. Please have a check.<br><br>'
mail_table=" <table cellpadding=0 cellspacing=0  border=1 style=width:100% >"
mail_table_title = "<tr style='background-color:rgb(0, 255, 0)'><th align=left>"+"ID"+"</th><th align=left>"+"DatabaseName"+"</th><th align=left>"+"SP_Name"+"</th><th align=left>"+"UpdateTime"+"</th></tr>"
rowId = 1
mail_msg = mail_msg + mail_msg2 + mail_table + mail_table_title

if not diff_list:
    file_name = 'N/A'
    update_time = 'N/A'
    mail_msg += "<tr><td cellpadding=5px bgcolor=#E6E6FA>" + str(rowId) + "</td><td cellpadding=5px bgcolor=#E6E6FA>" + DATABASE + "</td><td cellpadding=5px bgcolor=#E6E6FA>" + file_name + "</td><td cellpadding=5px bgcolor=#E6E6FA>" + update_time + "</td></tr>"
else:
    for diff in diff_list:
        file_name = diff[0]
        update_time = diff[1].strftime('%Y-%m-%d %H:%M:%S')
        mail_msg += "<tr><td cellpadding=5px bgcolor=#E6E6FA>" + str(rowId) + "</td><td cellpadding=5px bgcolor=#E6E6FA>" + DATABASE + "</td><td cellpadding=5px bgcolor=#E6E6FA>" + file_name + "</td><td cellpadding=5px bgcolor=#E6E6FA>" + update_time + "</td></tr>"
        rowId += 1

mail_msg = mail_msg + "</table><br>"

message = MIMEMultipart('related')
msgAlternative = MIMEMultipart('alternative')
message.attach(msgAlternative)

msgAlternative.attach(MIMEText(mail_msg, 'html', 'utf-8'))
subject = "[AzureSQL-QA]-Store Procedure Compare Monitor"
message['Subject'] = Header(subject, 'utf-8')
message['To'] = Header('Vincent Tang; Halin Huang; Hui Li', 'utf-8')
# to_address = ['vincent.tang@greendotcorp.com', 'halin.huang@greendotcorp.com', 'hli@greendotcorp.com']
to_address = 'vincent.tang@greendotcorp.com'
smtpObj.sendmail('AzureSQLAlert@greendotcorp.com', [to_address], message.as_string())

print("Successfully sent email")

